package com.grep.extra.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.grep.base.db.DBUtils;
import com.grep.base.util.UUIDGenerator;
import com.grep.extra.dao.BaseDao;
import com.grep.extra.dao.TaskDao;
import com.grep.extra.factory.data.impl.TaskUrl;
import com.grep.web.dto.SiteTaskDto;
import com.grep.web.dto.TaskNumber;

public class TaskDAOSQL implements TaskDao {
	private static Logger logger = LoggerFactory.getLogger(TaskDAOSQL.class);

	// 不使用继承，使用组合
	BaseDao baseDao = new BaseDaoImpl();

	public boolean insertTask(String insertTaskSQL) {
		return baseDao.insert(insertTaskSQL);
	}

	public boolean insertTaskURLs(String taskId, List<String> taskURLS) {
		for (String url : taskURLS) {
			insertTaskURL(taskId, url);
		}
		return true;
	}

	// 根据任务ID，得到该任务内容
	public SiteTaskDto getTaskByTaskID(String taskid) {

		SiteTaskDto siteTaskDto = new SiteTaskDto();

		String selectsql = "select * from site_tasks where id='" + taskid + "'";

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(selectsql);
			if (result.next()) {
				siteTaskDto.setId(taskid);
				siteTaskDto.setTaskAnalyse(result.getString("task_analyse"));
				siteTaskDto.setTaskDesc(result.getString("task_desc"));
				siteTaskDto.setTaskName(result.getString("task_name"));
				siteTaskDto.setTaskPolicy(result.getString("task_policy"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("there have a exception when query data from site_tasks");
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}
		return siteTaskDto;
	}

	public boolean insertTaskURL(String taskId, String taskURL) {
		String id = UUIDGenerator.getUUID();
		String sql = "insert into task_urls (id,task_id,task_url) values ('"
				+ id + "','" + taskId + "','" + taskURL + "')";
		return baseDao.insert(sql);
	}

	// 取得所有站点所有任务URL
	public List<TaskUrl> getTaskUrls() {
		String sql = " select taskurl.id,taskurl.task_id, sitetask.task_analyse ,"
				+ " sitetask.task_enname,"
				+ " taskurl.task_url"
				+ " from task_urls taskurl ,site_tasks sitetask where taskurl.task_id = sitetask.id";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TaskUrl> list = new ArrayList<TaskUrl>();
		try {
			conn = DBUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TaskUrl url = new TaskUrl();
				url.setTaskId(rs.getString("task_id"));
				url.setTaskUrl(rs.getString("task_url"));
				url.setTaskAnalyse(rs.getString("task_analyse"));
				url.setTaskEnName(rs.getString("task_enname"));
				url.setId(rs.getString("id"));

				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("there have a exception when query data from task_urls");
		} finally {
			DBUtils.close(rs);
			DBUtils.close(pstmt);
			DBUtils.close(conn);
		}
		return list;
	}

	// 取得某站点下所有任务的URl
	public List<TaskUrl> getTaskUrlsBySiteId(String siteid) {

		String sql = " select taskurl.id,taskurl.task_id, sitetask.task_analyse ,"
				+ " sitetask.task_enname,"
				+ " taskurl.task_url "
				+ " from task_urls taskurl ,site_tasks sitetask where taskurl.task_id = sitetask.id and sitetask.site_id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TaskUrl> list = new ArrayList<TaskUrl>();
		try {
			conn = DBUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, siteid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				TaskUrl url = new TaskUrl();
				url.setTaskId(rs.getString("task_id"));
				url.setTaskUrl(rs.getString("task_url"));
				url.setTaskAnalyse(rs.getString("task_analyse"));
				url.setTaskPolicy(rs.getString("task_policy"));
				url.setTaskEnName(rs.getString("task_enname"));
				url.setId(rs.getString("id"));

				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("there have a exception when query data from task_urls");
		} finally {
			DBUtils.close(rs);
			DBUtils.close(pstmt);
			DBUtils.close(conn);
		}
		return list;
	}

	// 取得某站点某一任务的URL
	public List<TaskUrl> getTaskUrlsByTaskId(String taskid) {
 
		
		String sql = "select id,task_url from task_urls where task_id='"
				+ taskid + "'";
		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;
		List<TaskUrl> list = new ArrayList<TaskUrl>();
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(sql);
			while (result.next()) {
				TaskUrl url = new TaskUrl();
				url.setTaskUrl(result.getString("task_url"));
				url.setId(result.getString("id"));
				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("there have a exception when query data from task_urls");
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}
		return list;
	}
	// 取得某站点某一任务的URL
	public List<TaskUrl> getTaskUrlsByTaskId(String taskid,int start,int pagesize) {
 
		
		String sql = "select id,task_url from task_urls where task_id='"
				+ taskid + "' order by id limit " + start + "," + pagesize;
		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;
		List<TaskUrl> list = new ArrayList<TaskUrl>();
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(sql);
			while (result.next()) {
				TaskUrl url = new TaskUrl();
				url.setTaskUrl(result.getString("task_url"));
				url.setId(result.getString("id"));
				list.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("there have a exception when query data from task_urls");
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}
		return list;
	}
	public List<TaskUrl> getTaskNamesbySiteName(String sitename) {

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;

		List<TaskUrl> listtaskname = new ArrayList<TaskUrl>();

		WebSiteDAOSQL webSiteDaosql = new WebSiteDAOSQL();

		String siteId = webSiteDaosql.getSiteIdBySiteName(sitename);

		String selecttasksql = "select id,task_name from site_tasks where site_id='"
				+ siteId + "'";
		// ResultSet result = baseDao.select(selecttasksql);
		try {

			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(selecttasksql);

			while (result.next()) {
				TaskUrl url = new TaskUrl();
				url.setId(result.getString("id"));
				url.setTaskName(result.getString("task_name"));
				listtaskname.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return listtaskname;

	}

	// 根据网站ID，以分页形式得到所属所有任务内容
	public List<TaskUrl> getTaskbySiteID(String siteid, int start, int pagesize) {

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;

		List<TaskUrl> listtascont = new ArrayList<TaskUrl>();

		String selecttasksql = "select * from site_tasks where site_id='"
				+ siteid + "' order by id limit " + start + "," + pagesize;
		try {

			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(selecttasksql);

			while (result.next()) {
				TaskUrl url = new TaskUrl();
				url.setTaskName(result.getString("task_name"));
				url.setTaskDesc(result.getString("task_desc"));
				url.setTaskPolicy(result.getString("task_policy"));
				url.setTaskAnalyse(result.getString("task_analyse"));
				url.setId(result.getString("id"));
				listtascont.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return listtascont;

	}

	public List<TaskUrl> getTaskBySiteID(String siteid) {

		List<TaskUrl> listtaskurl = new ArrayList<TaskUrl>();
		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;

		String selecttasksql = "select task_name,id from site_tasks where site_id='"
				+ siteid + "'";
		try {

			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(selecttasksql);

			while (result.next()) {
				TaskUrl url = new TaskUrl();
				url.setTaskName(result.getString("task_name"));
				url.setId(result.getString("id"));
				listtaskurl.add(url);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return listtaskurl;
	}

	// 根据任务名称，得到该任务ID
	public String getIdByTaskName(String name) {

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;

		String selectSql = "select id from site_tasks where task_name='" + name
				+ "'";

		String taskID = "";

		// ResultSet result = baseDao.select(selectSql);

		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(selectSql);
			if (result.next()) {
				taskID = result.getString("id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}
		return taskID;
	}

	// 根据任务id 删除该任务
	public boolean DeleteSiteTaskByTaskID(String taskid) {

		String deletesql = "delete from site_tasks where id='" + taskid + "'";

		return baseDao.insert(deletesql);
	}

	// 根据任务url id 删除该url
	public boolean DeleteTaskUrlByUrlId(String urlid) {

		String deletesql = "delete from task_urls where id='" + urlid + "'";

		return baseDao.insert(deletesql);
	}

	// 取得所有任务
	public List<SiteTaskDto> getAllTasks() {
		String querySql = "select id, task_name from site_tasks";

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;

		List<SiteTaskDto> tasks = new ArrayList<SiteTaskDto>();
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(querySql);
			while (result.next()) {
				SiteTaskDto dto = new SiteTaskDto();
				dto.setId(result.getString("id"));
				dto.setTaskName(result.getString("task_name"));
				tasks.add(dto);
			}
			return tasks;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return tasks;
	}

	public List<TaskNumber> getTaskNumbersById(String taskId, int count) {
		String querySql = "select id, `task-id`, task_collect_num, task_collect_date from task_numbers where `task-id`=? order by task_collect_date desc limit ?";

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;

		List<TaskNumber> tasks = new ArrayList<TaskNumber>();
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			PreparedStatement sta = conn.prepareStatement(querySql);
			sta.setString(1, taskId);
			sta.setInt(2, count);
			result = sta.executeQuery();

			while (result.next()) {
				TaskNumber taskNumber = new TaskNumber();
				taskNumber.setId(result.getString("id"));
				taskNumber.setTaskId(result.getString("task-id"));
				taskNumber.setTaskCollectNumber(result
						.getString("task_collect_num"));
				taskNumber.setTaskCollectDate(result
						.getTimestamp("task_collect_date"));
				tasks.add(taskNumber);
			}
			return tasks;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return tasks;
	}

	// 根据网站站点ID，得该任务的数量
	public int getTaskCount(String siteid) {

		int count = 0;

		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;
		String countsql = "select count(*) from site_tasks where site_id='"+siteid+"'";
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(countsql);
			if (result.next()) {
				count = result.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return count;
	}

	// 根据url ID 取得该url
	public TaskUrl getTaskUrlbyUrlId(String urlid) {

		TaskUrl taskurl = new TaskUrl();
		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;
		String selectsql = "select task_url from task_urls where id='" + urlid + "'";

		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(selectsql);
			while (result.next()) {
				taskurl.setTaskUrl(result.getString("task_url"));
				taskurl.setId(urlid);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("there have a exception when query data from task_urls");
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}
		return taskurl;
	}
	
	public int getTaskUrlCountbyTaskId(String taskid){
		
		int count=0;
		Connection conn = null;
		Statement stat = null;
		ResultSet result = null;
		String countsql = "select count(*) from task_urls where task_id='"+taskid+"'";
		try {
			conn = DBUtils.getConnection();
			stat = conn.createStatement();
			result = stat.executeQuery(countsql);
			if (result.next()) {
				count = result.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(result);
			DBUtils.close(stat);
			DBUtils.close(conn);
		}

		return count;
	}
	
	
	
}
